﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Windows.Forms;
using System.Net.Mail;

namespace DbTask
{
    class Program
    {
        private static readonly string toEmail = ConfigurationManager.AppSettings["SmtpTo"];

        static void Main(string[] args)
        {
            try
            {
                DataAccess da = new DataAccess();
                string downLoadFolder = Utils.DownloadFolder() + "\\";
                InfoMessage("Starting db tasks...");

                List<TaskItem> tasks = GetQueryList();
                foreach (TaskItem task in tasks)
                {
                    InfoMessage("Running task " + task.Name);
                    DataTable taskResult = da.ExecuteSQL(task.SqlText);
                    if (taskResult.Rows.Count == 0)
                    {
                        InfoMessage("Dont receive result");
                        continue;
                    }

                    string subject = taskResult.Rows[0]["Subject"].ToString();
                    if (Convert.ToInt32(taskResult.Rows[0]["Success"]) == 0)
                    {
                        InfoMessage("Task not success, subject: " + subject);
                        EmailSender.Send(toEmail, subject, "", false, null);
                        continue;
                    }

                    InfoMessage("Task run successfuly, subject: " + subject);
                    List<Attachment> att = new List<Attachment>();
                    foreach (DataRow dr in taskResult.Rows)
                    {
                        DataTable sqlResult = da.ExecuteSQL(dr["StoredProcedure"].ToString());
                        string attFileName = downLoadFolder + dr["AttachmentName"].ToString();
                        Utils.DataTableToCsv(sqlResult, attFileName);
                        Attachment attItem = new Attachment(attFileName, "text/plain");
                        att.Add(attItem);
                    }
                    InfoMessage("Send mail with " + att.Count.ToString() + " att");
                    EmailSender.Send(toEmail, subject, "", false, att);

                }
            }
            catch (Exception ex)
            {
                InfoMessage(ex.Message);
            }

            Console.WriteLine("Press any key to exit...");
            Console.ReadKey();
        }

        private static List<TaskItem> GetQueryList()
        {
            List<TaskItem> result = new List<TaskItem>();
            string filePath = Utils.AppFolder() + "\\diagnostics.xml";

            XDocument doc = XDocument.Load(filePath);
            foreach (XElement el in doc.Root.Elements())
            {
                TaskItem itm = new TaskItem();
                itm.Name = el.Attribute("Name").Value;
                itm.SqlText = el.Value;
                result.Add(itm);
            }

            return result;
        }

        private static void InfoMessage(string text)
        {
            Console.WriteLine(text);
            Utils.AddLog(text);
        }
        
    }

    public class TaskItem
    {
        public string Name { get; set; }
        public string SqlText { get; set; }
    }
}
